package org.jeecg.modules.veterans.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.system.entity.SysUserCountSta;
import org.jeecg.modules.system.vo.SysNameValueVo;
import org.jeecg.modules.veterans.entity.SysService;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * @Description: 优待服务信息表
 * @Author: jeecg-boot
 * @Date:   2022-04-06
 * @Version: V1.0
 */
public interface SysServiceMapper extends BaseMapper<SysService> {

//    /**
//     * 根据距离查找附近服务(Discount)
//     * @param lng
//     * @param lat
//     * @param distance
//     * @return
//     */
//    public List<SysService> queryDiscountListByCircle(@Param("lng")double lng,@Param("lat")double lat,@Param("distance")String distance,@Param("areas")String[] areas,@Param("type")String type,@Param("sort")String sort,@Param("searchCon")String searchCon,@Param("page")Long page,@Param("size")Long size);
//
//    /**
//     * 根据距离查找附近服务(Service)
//     * @param lng
//     * @param lat
//     * @param distance
//     * @return
//     */
//    public List<SysService> queryServiceListByCircle(@Param("lng")double lng,@Param("lat")double lat,@Param("distance")String distance,@Param("areas")String[] areas,@Param("sort")String sort,@Param("serviceType")String serviceType,@Param("page")Long page,@Param("size")Long size);
//
//    public Integer queryServiceMountByArea(@Param("area")int area);
//
//    public Integer queryServiceMountByAreaAndServiceType(@Param("area")int area,@Param("serviceType")String serviceType);


    @Select("SELECT\n" +
            "\ta.count as value, \n" +
            "\tc.item_text AS NAME \n" +
            "FROM\n" +
            "\t( SELECT service_type, COUNT(*) AS count FROM sys_service GROUP BY service_type ) a\n" +
            "\tLEFT JOIN sys_dict b ON b.dict_code = 'service_type'\n" +
            "\tLEFT JOIN sys_dict_item c ON c.dict_id = b.id \n" +
            "\tAND c.item_value = a.service_type \n" +
            "ORDER BY\n" +
            "\ta.service_type")
    List<SysNameValueVo> serviceType();

    @Select("SELECT \"宿城区\" as name,COUNT(*) as value FROM sys_service WHERE area = 1 AND state = 1\n" +
            "UNION\n" +
            "SELECT \"宿豫区\" as name,COUNT(*) as value FROM sys_service WHERE area = 2 AND state = 1\n" +
            "UNION\n" +
            "SELECT \"沭阳县\" as name,COUNT(*) as value FROM sys_service WHERE area = 3 AND state = 1\n" +
            "UNION\n" +
            "SELECT \"泗阳县\" as name,COUNT(*) as value FROM sys_service WHERE area = 4 AND state = 1\n" +
            "UNION\n" +
            "SELECT \"泗洪县\" as name,COUNT(*) as value FROM sys_service WHERE area = 5 AND state = 1 ")
    List<SysNameValueVo> serviceArea();

    @Select("SELECT * FROM\n" +
            "\t( SELECT COUNT(*) AS serviceCount FROM sys_service WHERE state = 1 ) AS serviceCount,\n" +
            "\t( SELECT COUNT(*) AS veteranCount FROM sys_veterans ) AS veteranCount,\n" +
            "\t( SELECT COUNT(*) AS userCount FROM sys_user WHERE del_flag = 0 ) AS userCount")
    SysUserCountSta userCount();
}
